package com.quanyan.ic.http.dao.sqlprovider;

import com.quanyan.ic.common.paging.Limit;
import org.apache.ibatis.jdbc.SQL;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;

import java.util.Map;

/**
 * Created by zhuo.shi on 2017/7/21.
 */
public class LightAlarmSqlProvider {

    private static Logger logger = LoggerFactory.getLogger(LightAlarmSqlProvider.class);

    public String queryLightAlarmList(final Map<String, Object> params, Limit limit) {

        StringBuilder sqlBuilder = new StringBuilder();
        SQL selectSql = new SQL() {
            {
                SELECT("*");
                FROM("venue_light_abnormal_day vl");
            }
        };
        addConditionSql(selectSql, params);
        sqlBuilder.append(selectSql.toString());

        sqlBuilder.append(" order by dt desc");

        if (limit != null) {
            sqlBuilder.append(" limit ");
            sqlBuilder.append(limit.getOffset());
            sqlBuilder.append(",");
            sqlBuilder.append(limit.getPageSize());
        }
        logger.info("customSQL: \n" + sqlBuilder.toString());

        return sqlBuilder.toString();
    }

    public String countLightAlarmList(final Map<String, Object> params) {

        SQL countSql = new SQL() {
            {
                SELECT("count(*)");
                FROM("venue_light_abnormal_day vl");
            }
        };

        addConditionSql(countSql, params);
        return countSql.toString();
    }

    private void addConditionSql(SQL sql, final Map<String, Object> params) {
        if (params.isEmpty()) {
            return;
        }
        sql.WHERE("1=1");
        if (params.get("cityId") != null) {
            sql.AND();
            sql.WHERE("city_id=" + params.get("cityId"));
        }
        if (!StringUtils.isEmpty(params.get("placeName"))) {
            sql.AND();
            sql.WHERE("place_name like '" + params.get("placeName") + "' ");
        }
        if (!StringUtils.isEmpty(params.get("startDate"))) {
            sql.AND();
            sql.WHERE("dt >= " + params.get("startDate"));
        }
        if (!StringUtils.isEmpty(params.get("endDate"))) {
            sql.AND();
            sql.WHERE("dt <= " + params.get("endDate"));
        }
    }

    public String totalLightAlarms(final Map<String, Object> params) {
        SQL selectSql = new SQL() {
            {
                SELECT(" sum(TIMESTAMPDIFF(\tMINUTE,\tdate_format(CONCAT('2017-10-08 ', start_time),'%Y-%c-%d %h:%i:%s'),date_format(\tCONCAT('2017-10-08 ', end_time),'%Y-%c-%d %h:%i:%s'))) as lightExceptionTime" +
                        ",city_id as cityId, city as cityName, count(*) as lCount");
                FROM("venue_light_abnormal_day");
                GROUP_BY("city_id");
            }
        };
        addConditionSql(selectSql, params);
        logger.info("totalLightAlarms: \n" + selectSql.toString());
        return selectSql.toString();
    }


    public String sumLightMinutes(final Map<String, Object> params) {
        SQL selectSql = new SQL() {
            {
                SELECT(" count(*) as lightCount,sum(duration) as lightMinutes");
                FROM("venue_light_abnormal_day");
            }
        };
        addConditionSql(selectSql, params);
        logger.info("sumVideoMinutes: \n" + selectSql.toString());
        return selectSql.toString();
    }


    public String queryLightList(final Map<String, Object> params) {
        StringBuilder sqlBuilder = new StringBuilder();
        SQL selectSql = new SQL() {
            {
                SELECT("cate,place_unit_id ,place_unit ,start_time as startTime ,end_time ,duration ,dt,2 as mold  ");
                FROM("venue_light_abnormal_day vl");
            }
        };
        addConditionSql(selectSql, params);
        logger.info("queryLightList: \n" + selectSql.toString());
        return selectSql.toString();
    }


    public String alarmsGroupByStart(final Map<String, Object> params) {
        SQL selectSql = new SQL() {
            {
                SELECT(" LEFT(start_time, 2) AS time,count(*) as count ");
                FROM("venue_light_abnormal_day");
                GROUP_BY("time");
            }
        };
        addConditionSql(selectSql, params);
        logger.info("alarmsGroupByStart: \n" + selectSql.toString());
        return selectSql.toString();
    }

}
